#
# Read/write XLSX (Office Open XML Excel) file format
#

import datetime

from openpyxl.reader.excel import load_workbook
from openpyxl.workbook import Workbook

from openpyxl.style import Color, Fill, NumberFormat

# Read file
wb = load_workbook(filename='simple.xlsx')
sheet_names = wb.get_sheet_names()
for sn in sheet_names:
	sheet = wb.get_sheet_by_name(sn)
	r = sheet.calculate_dimension()
	print '===== Reading sheet "%s", dimensions "%s"' % (sheet.title, r)
	for row in sheet.range(r):
		for cell in row:
			print ' Cell %s, value %s, type %s' % (
					cell.address, cell.value, cell.data_type)

# Write file
wb_out = Workbook()
for i,sheet in enumerate(wb.worksheets):
	new_sheet = wb_out.create_sheet(i)
	new_sheet.title = sheet.title
all_sheets = zip(wb_out.worksheets, wb.worksheets)
for s in all_sheets:
	print s[1].columns[0][0].column
	r = s[1].calculate_dimension()
	print r
	for row in s[1].range(r):
		for cell in row:
			cs = s[1].get_style(cell.get_coordinate())
			print ' Cell %s, value %s, type %s, %s:%s' % (
					cell.address, cell.value, cell.data_type,
					cs.font.bold, cs.font.italic)
			new_cell = s[0].cell(cell.address)
			if cell.value is None:
				continue
			#new_cell.set_value_explicit(value=cell.value, data_type=cell.data_type)
			#new_cell.data_type= cell.data_type
			new_cell.value = cell.value
			#new_cell.style.borders = cell.style.borders
			#new_cell.style.protection = cell.style.protection
			#new_cell.style.alignment = cell.style.alignment
			new_cell.style.font.name = cell.style.font.name
			new_cell.style.font.size = cell.style.font.size
			new_cell.style.font.bold = True
			new_cell.style.font.italic = True
			new_cell.style.font.color = Color(cell.style.font.color)
			new_cell.style.fill.fill_type = cell.style.fill.fill_type
			new_cell.style.fill.rotation = cell.style.fill.rotation
			new_cell.style.fill.start_color = Color(cell.style.fill.start_color)
			new_cell.style.fill.end_color = Color(cell.style.fill.end_color)
			new_cell.style.number_format._set_format_code(cell.style.number_format._get_format_code())
for s in all_sheets:
	for col in s[1].columns:
		col_name = col[0].column
		s[0].column_dimensions[col_name].width = s[1].column_dimensions[col_name].width

wb_out.save(filename='new.xlsx')

# Write a file
wb_out = Workbook()
sheet = wb_out.create_sheet(0)
#-----
c = sheet.cell('A1')
c.value = 'BOLD'
c.style.font.bold = True
#-----
c = sheet.cell('B1')
c.value = 'ITALIC'
c.style.font.italic = True
#-----
c = sheet.cell('C1')
c.value = 'YELLOW'
c.style.font.color = Color(Color.YELLOW)
#-----
c = sheet.cell('D1')
c.value = 'RED'
c.style.fill.fill_type = Fill.FILL_SOLID
c.style.fill.start_color = Color(Color.RED)
c.style.fill.end_color = Color(Color.RED)
#-----
c = sheet.cell('A2')
c.value = datetime.datetime.utcnow()
c.style.number_format._set_format_code(NumberFormat.FORMAT_DATE_DATETIME)
#-----
c = sheet.cell('B2')
c.value = datetime.datetime.utcnow()
c.style.number_format._set_format_code(NumberFormat.FORMAT_DATE_YYYYMMDD2)
#-----
c = sheet.cell('C2')
c.value = datetime.datetime.utcnow()
c.style.number_format._set_format_code(NumberFormat.FORMAT_DATE_XLSX16)
#-----
c = sheet.cell('A3')
c.value = 12345.67
c.style.number_format._set_format_code(NumberFormat.FORMAT_NUMBER_COMMA_SEPARATED2)
#-----
c = sheet.cell('B3')
c.value = 0.0067
c.style.number_format._set_format_code(NumberFormat.FORMAT_NUMBER_COMMA_SEPARATED1)
#-----
c = sheet.cell('C3')
c.value = 0.0067
c.style.number_format._set_format_code(NumberFormat.FORMAT_NUMBER_00)
#-----
c = sheet.cell('D3')
c.value = 0.67
c.style.number_format._set_format_code(NumberFormat.FORMAT_PERCENTAGE_00)
#+++++
sheet.column_dimensions['A'].width = 15
wb_out.save(filename='test.xlsx')

